热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

Oracle分析函数first_value()和last_value()的用法及原理

本文介绍了Oracle分析函数first_value()和last_value()的用法和原理,以及在查询销售记录日期和部门中的应用。通过示例和解释,详细说明了first_value()和last_value()的功能和不同之处。同时,对于last_value()的结果出现不一样的情况进行了解释,并提供了理解last_value()默认统计范围的方法。该文对于使用Oracle分析函数的开发人员和数据库管理员具有参考价值。

篇首语:本文由编程笔记#小编为大家整理,主要介绍了Oracle分析函数-first_value()和last_value()相关的知识,希望对你有一定的参考价值。


first_value()last_value()字面意思已经很直观了,取首尾记录值。
例:查询部门最早发生销售记录日期和最近发生的销售记录日期

select
dept_id
,sale_date
,goods_type
,sale_cnt
,first_value(sale_date) over (partition by dept_id order by sale_date) first_value
,last_value(sale_date) over (partition by dept_id order by sale_date desc) last_value
from criss_sales;

 
看结果first_value()很直观,不用多解释
但是,last_value()值,部门D01不是应该为2014/6/12,部门D02不是应该为2014/5/2吗?为什么会每条记录都不一样?
可以这样去理解:last_value()默认统计范围是 rows between unbounded preceding and current row
验证一下:

select
dept_id
,sale_date
,goods_type
,sale_cnt
,first_value(sale_date) over (partition by dept_id order by sale_date) first_value
,last_value(sale_date) over (partition by dept_id order by sale_date desc) last_value
,last_value(sale_date) over (partition by dept_id order by sale_date rows between unbounded preceding and unbounded following) last_value_all
from criss_sales;

全统计的情况下得到的last_value()值,部门D01为2014/6/12,部门D02为2014/5/2。




Oracle开发之:窗口函数 (转) rows between unbounded preceding and current row


一、窗口函数简介:

到目前为止,我们所学习的分析函数在计算/统计一段时间内的数据时特别有用,但是假如计算/统计需要随着遍历记录集的每一条记录而进行呢?举些例子来说:

①列出每月的订单总额以及全年的订单总额
②列出每月的订单总额以及截至到当前月的订单总额
③列出上个月、当月、下一月的订单总额以及全年的订单总额
④列出每天的营业额及一周来的总营业额
⑤列出每天的营业额及一周来每天的平均营业额

仔细回顾一下前面我们介绍到的分析函数,我们会发现这些需求和前面有一些不同:前面我们介绍的分析函数用于计算/统计一个明确的阶段/记录集,而这里有部分需求例如2,需要随着遍历记录集的每一条记录的同时进行统计。

也即是说:统计不止发生一次,而是发生多次。统计不至发生在记录集形成后,而是发生在记录集形成的过程中。

这就是我们这次要介绍的窗口函数的应用了。它适用于以下几个场合:

①通过指定一批记录:例如从当前记录开始直至某个部分的最后一条记录结束
②通过指定一个时间间隔:例如在交易日之前的前30天
③通过指定一个范围值:例如所有占到当前交易量总额5%的记录


二、窗口函数示例-全统计:

下面我们以需求:列出每月的订单总额以及全年的订单总额为例,来看看窗口函数的应用。

【1】测试环境:

SQL> desc orders;
名称 是否为空? 类型
----------------------- -------- ----------------
MONTH NUMBER(2)
TOT_SALES NUMBER
SQL>

【2】测试数据:

SQL> select * from orders;
MONTH TOT_SALES
---------- ----------
1 610697
2 428676
3 637031
4 541146
5 592935
6 501485
7 606914
8 460520
9 392898
10 510117
11 532889
12 492458
已选择12行。

【3】测试语句:

回忆一下前面《Oracle开发专题之:分析函数(OVER)》一文中,我们使用了sum(sum(tot_sales)) over (partition by region_id) 来统计每个分区的订单总额。现在我们要统计的不单是每个分区,而是所有分区,partition by region_id在这里不起作用了。

Oracle为这种情况提供了一个子句:rows between ... preceding and ... following。从字面上猜测它的意思是:在XXX之前和XXX之后的所有记录,实际情况如何让我们通过示例来验证:

SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between unbounded preceding and unbounded following) total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
1 610697 6307766
2 428676 6307766
3 637031 6307766
4 541146 6307766
5 592935 6307766
6 501485 6307766
7 606914 6307766
8 460520 6307766
9 392898 6307766
10 510117 6307766
11 532889 6307766
12 492458 6307766
已选择12行。

绿色高亮处的代码在这里发挥了关键作用,它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次,这时相当费时的!但至少我们解决了问题。

unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录,也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢?看看下面的结果:

SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between 1 preceding and unbounded following) all_sales
5 from orders
6 group by month;
MONTH MONTH_SALES ALL_SALES
---------- ----------- ----------
1 610697 6307766
2 428676 6307766
3 637031 5697069
4 541146 5268393
5 592935 4631362
6 501485 4090216
7 606914 3497281
8 460520 2995796
9 392898 2388882
10 510117 1928362
11 532889 1535464
12 492458 1025347
已选择12行。

很明显这个语句错了。实际1在这里不是从第1条记录开始的意思,而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数,如同unbounded就是告诉oracle不管当前记录是第几条,只要前面有多少条记录,都列入统计的范围。


三、窗口函数进阶-滚动统计(累积/均值):

考虑前面提到的第2个需求:列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和,依此类推。

很明显这个需求需要在统计第N月的订单总额时,还要再统计这N个月来的订单总额之和。想想上面的语句,假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊!很幸运的是Oracle考虑到了我们这个需求,为此我们只需要将语句稍微改成: curreent row就可以了。

SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and current row) current_total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
1 610697 610697
2 428676 1039373
3 637031 1676404
4 541146 2217550
5 592935 2810485
6 501485 3311970
7 606914 3918884
8 460520 4379404
9 392898 4772302
10 510117 5282419
11 532889 5815308
12 492458 6307766
已选择12行。

现在我们能得到滚动的销售总额了!下面这个统计结果看起来更加完美,它展现了所有我们需要的数据:

SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and current row) current_total_sales,
5 sum(sum(tot_sales)) over(order by month
6 rows between unbounded preceding and unbounded following) total_sales
7 from orders
8 group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES TOTAL_SALES
---------- ----------- ------------------- -----------
1 610697 610697 6307766
2 428676 1039373 6307766
3 637031 1676404 6307766
4 541146 2217550 6307766
5 592935 2810485 6307766
6 501485 3311970 6307766
7 606914 3918884 6307766
8 460520 4379404 6307766
9 392898 4772302 6307766
10 510117 5282419 6307766
11 532889 5815308 6307766
12 492458 6307766 6307766
已选择12行。

在一些销售报表中我们会时常看到求平均值的需求,有时可能是针对全年的数据求平均值,有时会是针对截至到当前的所有数据求平均值。很简单,只需要将:
sum(sum(tot_sales))换成avg(sum(tot_sales))即可。


四、窗口函数进阶-根据时间范围统计:

前面我们说过,窗口函数不单适用于指定记录集进行统计,而且也能适用于指定范围进行统计的情况,例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额:

select trunc(order_dt) day,
sum(sale_price) daily_sales,
avg(sum(sale_price)) over (order by trunc(order_dt)
range between interval '2' day preceding
and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null
and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
and to_date('31-jul-2001','dd-mon-yyyy')

为了对指定范围进行统计,Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天,后2天范围内的记录,并统计其销售平均值。


五、窗口函数进阶-first_value/last_value:

Oracle提供了2个额外的函数:first_value、last_value,用于在窗口记录集中查找第一条记录和最后一条记录。假设我们的报表需要显示当前月、上一个月、后一个月的销售情况,以及每3个月的销售平均值,这两个函数就可以派上用场了。

select month,
first_value(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) prev_month,

sum(tot_sales) monthly_sales,

last_value(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) next_month,

avg(sum(tot_sales)) over (order by month
rows between 1 preceding and 1 following) rolling_avg
from orders
where year = 2001
and region_id = 6
group by month
order by month;

首先我们来看:rows between 1 preceding and 1 following告诉Oracle在当前记录的前一条、后一条范围内查找并统计,而first_valuelast_value在这3条记录中至分别找出第一条、第三条记录,这样我们就轻松地得到相邻三个月的销售记录及平均值了!


六、窗口函数进阶-比较相邻记录:

通过第五部分的学习,我们知道了如何利用窗口函数来显示相邻的记录,现在假如我们想每次显示当月的销售额和上个月的销售额,应该怎么做呢?

从第五部分的介绍我们可以知道,利用first_value(sum(tot_sales) over (order by month rows between 1 preceding and 0 following))就可以做到了,其实Oracle还有一个更简单的方式让我们来比较2条记录,它就是lag函数。

leg函数类似于precedingfollowing

子句,它能够通过和当前记录的相对位置而被应用,在比较同一个相邻的记录集内两条相邻记录的时候特别有用。

select month,
sum(tot_sales) monthly_sales,
lag(sum(tot_sales), 1) over (order by month) prev_month_sales
from orders
where year = 2001
and region_id = 6
group by month
order by month;

lag(sum(tot_sales),1)中的1表示以1月为基准。

 


推荐阅读
  • 探索偶数次幂二项式系数的求和方法及其数学意义 ... [详细]
  • Python 实战:异步爬虫(协程技术)与分布式爬虫(多进程应用)深入解析
    本文将深入探讨 Python 异步爬虫和分布式爬虫的技术细节,重点介绍协程技术和多进程应用在爬虫开发中的实际应用。通过对比多进程和协程的工作原理,帮助读者理解两者在性能和资源利用上的差异,从而在实际项目中做出更合适的选择。文章还将结合具体案例,展示如何高效地实现异步和分布式爬虫,以提升数据抓取的效率和稳定性。 ... [详细]
  • 利用Python进行学生学业表现评估与成绩预测分析
    利用Python进行学生学业表现评估与成绩预测分析 ... [详细]
  • 在Python多进程编程中,`multiprocessing`模块是不可或缺的工具。本文详细探讨了该模块在多进程管理中的核心原理,并通过实际代码示例进行了深入分析。文章不仅总结了常见的多进程编程技巧,还提供了解决常见问题的实用方法,帮助读者更好地理解和应用多进程编程技术。 ... [详细]
  • MySQL索引详解及其优化策略
    本文详细解析了MySQL索引的概念、数据结构及管理方法,并探讨了如何正确使用索引以提升查询性能。文章还深入讲解了联合索引与覆盖索引的应用场景,以及它们在优化数据库性能中的重要作用。此外,通过实例分析,进一步阐述了索引在高读写比系统中的必要性和优势。 ... [详细]
  • 本文深入探讨了二叉树路径和问题的算法优化方法。具体而言,给定一棵二叉树,需要找出所有从根节点到叶节点的路径,其中各节点值的总和等于指定的目标值。通过详细分析和优化,提出了一种高效的解决方案,并通过多个样例验证了其有效性和性能。 ... [详细]
  • Node.js 配置文件管理方法详解与最佳实践
    本文详细介绍了 Node.js 中配置文件管理的方法与最佳实践,涵盖常见的配置文件格式及其优缺点,并提供了多种实用技巧和示例代码,帮助开发者高效地管理和维护项目配置,具有较高的参考价值。 ... [详细]
  • 如何高效启动大数据应用之旅?
    在前一篇文章中,我探讨了大数据的定义及其与数据挖掘的区别。本文将重点介绍如何高效启动大数据应用项目,涵盖关键步骤和最佳实践,帮助读者快速踏上大数据之旅。 ... [详细]
  • 本文深入探讨了 hCalendar 微格式在事件与时间、地点相关活动标记中的应用。作为微格式系列文章的第四篇,前文已分别介绍了 rel 属性用于定义链接关系、XFN 微格式增强链接的人际关系描述以及 hCard 微格式对个人和组织信息的描述。本次将重点解析 hCalendar 如何通过结构化数据标记,提高事件信息的可读性和互操作性。 ... [详细]
  • 在《Python编程基础》课程中,我们将深入探讨Python中的循环结构。通过详细解析for循环和while循环的语法与应用场景,帮助初学者掌握循环控制语句的核心概念和实际应用技巧。此外,还将介绍如何利用循环结构解决复杂问题,提高编程效率和代码可读性。 ... [详细]
  • 如何在Android设备上通过应用程序创建浏览器书签 ... [详细]
  • PyCharm 作为 JetBrains 出品的知名集成开发环境(IDE),提供了丰富的功能和强大的工具支持,包括项目视图、代码结构视图、代码导航、语法高亮、自动补全和错误检测等。本文详细介绍了 PyCharm 的高级使用技巧和程序调试方法,旨在帮助开发者提高编码效率和调试能力。此外,还探讨了如何利用 PyCharm 的插件系统扩展其功能,以满足不同开发场景的需求。 ... [详细]
  • 投融资周报 | Circle 达成 4 亿美元融资协议,唯一艺术平台 A 轮融资超千万美元 ... [详细]
  • 每日词汇精选——第50天- **character** (n./v.): 字符、符号、角色、个性- **screenshot** (n.): 屏幕截图、界面快照、画面捕捉- **notification** (n.): 通知、通告、提示信息、提醒窗口这些词汇在日常交流和技术文档中都非常常见,掌握它们有助于提升语言表达的准确性和专业性。 ... [详细]
  • 探索聚类分析中的K-Means与DBSCAN算法及其应用
    聚类分析是一种用于解决样本或特征分类问题的统计分析方法,也是数据挖掘领域的重要算法之一。本文主要探讨了K-Means和DBSCAN两种聚类算法的原理及其应用场景。K-Means算法通过迭代优化簇中心来实现数据点的划分,适用于球形分布的数据集;而DBSCAN算法则基于密度进行聚类,能够有效识别任意形状的簇,并且对噪声数据具有较好的鲁棒性。通过对这两种算法的对比分析,本文旨在为实际应用中选择合适的聚类方法提供参考。 ... [详细]
author-avatar
手机用户2502856895
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有